using System;
using System.Data;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using Clue_Less_Shared;
using Clue_Less_Shared.Enumerations;

namespace Clue_Less_WS
{
    /// <summary>
    /// Class responsible for storing and retrieving game data in/from SQL database.
    /// </summary>
    public class GamePersistance
    {
        private string ConnectionString = ConfigurationManager.ConnectionStrings["ClueLessData"].ConnectionString;

        /// <summary>
        /// Default constructor
        /// </summary>
        public GamePersistance()
        {
            //TODO: Add insert new game
        }

        /// <summary>
        /// Returns game status
        /// </summary>
        /// <param name="gameID">ID of the game</param>
        /// <returns>Game Status enumeration</returns>
        internal GameStatus GetGameStatus(int gameID)
        {
            SqlConnection cn = null;
            try
            {

                cn = new SqlConnection(ConnectionString);
                cn.Open();
                string sql = "SELECT game_status_id FROM game WHERE game_id = " + gameID;
                SqlCommand cmd = new SqlCommand(sql, cn);
                object status = cmd.ExecuteScalar();
                if (status == null)
                    throw new ClueLessExceptions("Failed to locate game in the database.", null);
                else
                    return (GameStatus)Enum.Parse(typeof(GameStatus), status.ToString());
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Could not extract game status." + e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns game envelope
        /// </summary>
        /// <param name="gameID">ID of the game</param>
        /// <returns>Game Status enumeration</returns>
        internal Envelope GetEnvelope(int gameID)
        {
            SqlConnection cn = null;
            SqlDataReader dr = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                string sql = "SELECT envelope_weapon, envelope_room, envelope_character FROM game WHERE game_id = " + gameID;
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                dr.Read();


                WeaponCard sw = new WeaponCard((Weapons)Enum.Parse(typeof(Weapons), dr.GetString(0)));
                RoomCard sr = new RoomCard((Rooms)Enum.Parse(typeof(Rooms), dr.GetString(1)));
                CharacterCard sc = new CharacterCard((Characters)Enum.Parse(typeof(Characters), dr.GetString(2)));
                
                Envelope env = new Envelope(sc, sw, sr);
                dr.Close();

                return env;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Could not extract game envelope." + e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

		/// <summary>
		/// Returns true if player has his/her turn
		/// </summary>
		/// <param name="gameID">Game ID</param>
		/// <param name="playerID">Player ID</param>
		/// <returns>true if player has his/her turn</returns>
        internal bool IsMyTurn(int gameID, int playerID)
		{
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                string sql = "SELECT game_player_turn FROM game WHERE game_id = " + gameID;
                SqlCommand comm = new SqlCommand(sql, cn);
                
                object turn = comm.ExecuteScalar();
                if (turn == null)
                    throw new ClueLessExceptions("Failed to get player turn.", null);

                if ((int)turn == playerID)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
		}

        /// <summary>
        /// Creates a new game.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="maxPlayers"></param>
        /// <returns></returns>
        internal int CreateGame(string name, int maxPlayers)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter("@game_human_players", maxPlayers));
                param.Add(new SqlParameter("@game_name", name));
                param.Add(new SqlParameter("@game_id", SqlDbType.Int));
                param[2].Direction = ParameterDirection.Output;

                SqlCommand comm = new SqlCommand("sp_create_game", cn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(param.ToArray());
                int rows = comm.ExecuteNonQuery();
                int id = (int)comm.Parameters["@game_id"].Value;

                if (rows == 0)
                    throw new ClueLessExceptions("Failed to create new game.", null);
                else
                    return id;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Created a new player in the database
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <param name="character">Character</param>
        /// <param name="name">Player name</param>
        /// <returns>New player ID</returns>
        internal int LoginPlayer(int gameID, Characters character, string name)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter("@character_id", (int)character));
                param.Add(new SqlParameter("@game_id", gameID));
                param.Add(new SqlParameter("@player_name", name));
                param.Add(new SqlParameter("@player_id", SqlDbType.Int));
                param[3].Direction = ParameterDirection.Output;

                SqlCommand comm = new SqlCommand("sp_create_player", cn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(param.ToArray());
                int rows = comm.ExecuteNonQuery();
                int id = (int)comm.Parameters["@player_id"].Value;

                if (rows == 0)
                    throw new ClueLessExceptions("Failed to create new player.", null);
                else
                    return id;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Stores suggestion in a DB
        /// </summary>
        /// <param name="gameID"></param>
        /// <param name="playerID"></param>
        /// <param name="room"></param>
        /// <param name="weapon"></param>
        /// <param name="character"></param>
        /// <returns></returns>
        internal void InsertSuggestion(int gameID, int playerID, Rooms room, Weapons weapon, Characters character)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "INSERT INTO suggestion (game_id, player_id, weapon, room, character) VALUES " +
                    " (" + gameID + ", " + playerID + ", '" + weapon.ToString() + "', '" + room.ToString() +
                    "', '" + character.ToString() + "')";

                SqlCommand comm = new SqlCommand(sql, cn);
                int rows = comm.ExecuteNonQuery();
                
                if (rows == 0)
                    throw new ClueLessExceptions("Failed to save suggestion.", null);
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Stores suggestion
        /// </summary>
        /// <param name="gameID"></param>
        /// <param name="suggestion"></param>
        internal void SaveSuggestion(int gameID, Suggestion suggestion)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "UPDATE suggestion SET " +
                    "is_disproved = '" + suggestion.IsDisproved.ToString() + "'," +
                    "player_id = " + suggestion.PlayerID + "," +
                    "weapon = '" + suggestion.SuspectWeapon.Name + "'," +
                    "room = '" + suggestion.SuspectRoom.Name + "'," +
                    "character = '" + suggestion.SuspectCharacter.Name + "'," +
                    "disproval_card = '" + (suggestion.DisprovalCard == null ? "" : suggestion.DisprovalCard.Name) + "'," +
                    "disprover = " + suggestion.DisproverID +
                    " WHERE suggestion_id = " + suggestion.SuggestionID;

                SqlCommand comm = new SqlCommand(sql, cn);
                int rows = comm.ExecuteNonQuery();

                if (rows == 0)
                    throw new ClueLessExceptions("Failed to save suggestion.", null);
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns latest suggestion
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        internal Suggestion GetSuggestion(int gameID)
        {
            SqlConnection cn = null;
            SqlDataReader dr = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "SELECT TOP 1 suggestion_id, is_disproved, s.player_id, weapon, room, character, disproval_card, disprover, c.character_name, cc.character_name " +
                        "FROM suggestion s, player p, player pp, character c, character cc " +
                        "WHERE s.game_id = " + gameID + " AND s.player_id = p.player_id AND s.disprover = pp.player_id " +
                        "AND c.character_id = p.character_id AND cc.character_id = pp.character_id " +
                        "ORDER BY suggestion_id DESC";
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                dr.Read();

                Suggestion suggestion  = new Suggestion();
                suggestion.SuggestionID = dr.GetInt32(0);
                suggestion.IsDisproved = dr.GetBoolean(1);
                suggestion.PlayerID = dr.GetInt32(2);
                suggestion.SuspectWeapon = new WeaponCard((Weapons)Enum.Parse(typeof(Weapons), dr.GetString(3)));
                suggestion.SuspectRoom = new RoomCard((Rooms)Enum.Parse(typeof(Rooms), dr.GetString(4)));
                suggestion.SuspectCharacter = new CharacterCard((Characters)Enum.Parse(typeof(Characters), dr.GetString(5)));
                suggestion.DisprovalCard = Card.CreateCardFromString(dr.GetString(6));
                suggestion.DisproverID = dr.GetInt32(7);
                suggestion.PlayerName = dr.GetString(8);
                suggestion.DisproverName = dr.GetString(9);
                
                dr.Close();

                return suggestion;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Could not extract game suggestion." + e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns list of all suggestions
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        internal List<Suggestion> GetSuggestions(int gameID)
        {
            SqlConnection cn = null;
            SqlDataReader dr = null;
            List<Suggestion> suggestions = new List<Suggestion>();
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "SELECT suggestion_id, is_disproved, s.player_id, weapon, room, character, disproval_card, disprover, c.character_name, cc.character_name " +
                        "FROM suggestion s, player p, player pp, character c, character cc " +
                        "WHERE s.game_id = " + gameID + " AND s.player_id = p.player_id AND s.disprover = pp.player_id " +
                        "AND c.character_id = p.character_id AND cc.character_id = pp.character_id " +
                        "ORDER BY suggestion_id DESC";
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    Suggestion suggestion = new Suggestion();
                    suggestion.SuggestionID = dr.GetInt32(0);
                    suggestion.IsDisproved = dr.GetBoolean(1);
                    suggestion.PlayerID = dr.GetInt32(2);
                    suggestion.SuspectWeapon = new WeaponCard((Weapons)Enum.Parse(typeof(Weapons), dr.GetString(3)));
                    suggestion.SuspectRoom = new RoomCard((Rooms)Enum.Parse(typeof(Rooms), dr.GetString(4)));
                    suggestion.SuspectCharacter = new CharacterCard((Characters)Enum.Parse(typeof(Characters), dr.GetString(5)));
                    suggestion.DisprovalCard = Card.CreateCardFromString(dr.GetString(6));
                    suggestion.DisproverID = dr.GetInt32(7);
                    suggestion.PlayerName = dr.GetString(8);
                    suggestion.DisproverName = dr.GetString(9);
                    suggestions.Add(suggestion);
                }
                dr.Close();

                return suggestions;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Could not extract game suggestion." + e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Adds a message to chat table.
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <param name="playerID">Player ID</param>
        /// <param name="message">Message</param>
        internal void SendChatMessage(int gameID, int playerID, string message)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                if (message.Length > 500)
                {
                    message = message.Substring(0, 500);
                }

                string sql = "INSERT INTO chat (game_id, player_id, chat_dt, chat_message) " +
                " VALUES (" + gameID + "," + playerID + ",GETDATE(),'" + message.Replace("'", "''") + "')";

                SqlCommand comm = new SqlCommand(sql, cn);
                int count = comm.ExecuteNonQuery();
                if (count != 1)
                {
                    throw new ClueLessExceptions("Failed to send a message.", null);
                }
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns array of characters with waiting status
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <returns>Array of ComboBoxItems where value is character name and label is the player name</returns>
        internal ComboBoxItem[] GetCharactersWaiting(int gameID)
        {
            string sql = "SELECT  c.character_name, p.player_name FROM player p, character c WHERE p.character_id = c.character_id AND p.game_id = " + gameID;
            return GetList(sql);
        }

        /// <summary>
        /// Returns available characters as array of ComboBoxItems (for drop down list). Used at the beginning of the game.
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <returns>Array of ComboBoxItems</returns>
        internal ComboBoxItem[] GetAvailableCharacters(int gameID)
        {
            string sql = "SELECT  CAST(character_id AS varchar(2)), character_name FROM character WHERE character_id NOT IN (SELECT character_id FROM player WHERE game_id = " + gameID + ")";
            return GetList(sql);
        }

        /// <summary>
        /// Returns available games as array of ComboBoxItems (for drop down list). Used at the beginning of the game.
        /// </summary>
        /// <returns></returns>
        internal ComboBoxItem[] GetOpenGameList()
        {
            string sql = "SELECT CAST(game_id AS VarChar(2)), game_name + ' (' + CAST(game_human_players AS VarChar(2)) + ' players)' FROM game WHERE  (game_status_id = 0) ORDER BY game_name";
            return GetList(sql);
        }

        /// <summary>
        /// Starts the game by changing game status and setting player locations
        /// </summary>
        /// <param name="gameID"></param>
        internal void StartGame(int gameID)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter("@game_id", gameID));
                
                SqlCommand comm = new SqlCommand("sp_start_game", cn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(param.ToArray());
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Changes game turn
        /// </summary>
        /// <param name="gameID">Game ID</param>
        internal void ChangeTurn(int gameID)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter("@game_id", gameID));

                SqlCommand comm = new SqlCommand("sp_change_turn", cn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(param.ToArray());
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Changes game status to complete
        /// </summary>
        /// <param name="gameID"></param>
        internal void FinishGame(int gameID)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "UPDATE game SET game_status_id = 2 WHERE game_id = " + gameID;

                SqlCommand comm = new SqlCommand(sql, cn);
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Changes player status to disabled
        /// </summary>
        /// <param name="gameID"></param>
        /// <param name="playerID"></param>
        internal void QuitGame(int gameID, int playerID)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "UPDATE player SET player_status = 2 WHERE game_id = " + gameID + " AND player_id = " + playerID;

                SqlCommand comm = new SqlCommand(sql, cn);
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns total number of required human players.
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        internal short GetHumanPlayerLimit(int gameID)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                SqlCommand cmd = new SqlCommand("SELECT game_human_players FROM game WHERE game_id = " + gameID, cn);
                object count = cmd.ExecuteScalar();

                if (count == null)
                    throw new ClueLessExceptions("Failed to select number of human players.", null);
                else
                    return (short)count;
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns lits of players in the game
        /// </summary>
        /// <param name="gameID"></param>
        /// <param name="all">If true returns human and non-human players</param>
        /// <returns></returns>
        internal List<Character> GetCharacters(int gameID, bool all)
        {
            SqlDataReader dr = null;
            List<Character> items = new List<Character>();
            try
            {
                string sql = "SELECT player_id, character_name, player_name, player_cards, player_position_x, player_position_y, player_human, player_status, player_idx " + 
                    "FROM player p, character c WHERE p.character_id = c.character_id AND game_id = " + gameID + 
                    (all ? "" : " AND player_human = 'True'");
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    Characters ctype = (Characters)Enum.Parse(typeof(Characters), dr.GetString(1));
                    Character character = new Character(ctype, -1, -1);
                    character.PlayerID = dr.GetInt32(0);
                    character.XPosition = dr.GetInt16(4);
                    character.YPosition = dr.GetInt16(5);
                    character.Index = dr.GetInt16(8);
                    character.Status = (PlayerStatus)Enum.Parse(typeof(PlayerStatus), dr.GetInt32(7).ToString());

                    if (dr.GetBoolean(6))
                    {
                        Player player = new Player(character);
                        
                        player.PlayerName = (dr.IsDBNull(2) ? "" : dr.GetString(2));
                        string player_cards = (dr.IsDBNull(3) ? "" : dr.GetString(3));
                        player.Cards.Clear();
                        if (player_cards.Length > 0)
                        {
                            string[] cards = player_cards.Split('|');
                            foreach (string card in cards)
                            {
                                player.Cards.Add(Card.CreateCardFromString(card));
                            }
                        }
                        items.Add(player);
                    }
                    else
                    {
                        items.Add(character);
                    }
                }

                dr.Close();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Failed to load the list. " + e.Message, e);
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return items;
        }

        /// <summary>
        /// Return player instance
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <param name="playerID">Player ID</param>
        /// <returns></returns>
        internal Character GetCharacter(int gameID, int playerID)
        {
            SqlDataReader dr = null;
            try
            {
                string sql = "SELECT player_id, character_name, player_name, player_cards, player_position_x, player_position_y, player_human, player_status, player_idx " +
                    "FROM player p, character c WHERE p.character_id = c.character_id AND game_id = " + gameID +
                    " AND player_id = " + playerID;
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {

                    Characters ctype = (Characters)Enum.Parse(typeof(Characters), dr.GetString(1));
                    Character character = new Character(ctype, -1, -1);
                    character.XPosition = dr.GetInt16(4);
                    character.YPosition = dr.GetInt16(5);
                    character.Status = (PlayerStatus)Enum.Parse(typeof(PlayerStatus), dr.GetInt32(7).ToString());
                    character.Index = dr.GetInt16(8);

                    if (dr.GetBoolean(6))
                    {
                        Player player = new Player(character);
                        player.PlayerID = dr.GetInt32(0);

                        player.PlayerName = (dr.IsDBNull(2) ? "" : dr.GetString(2));
                        string player_cards = (dr.IsDBNull(3) ? "" : dr.GetString(3));
                        player.Cards.Clear();
                        if (player_cards.Length > 0)
                        {
                            string[] cards = player_cards.Split('|');
                            foreach (string card in cards)
                            {
                                player.Cards.Add(Card.CreateCardFromString(card));
                            }
                        }
                        return player;
                    }
                    else
                    {
                        return character;
                    }
                }
                return null;
                dr.Close();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Failed to load the list. " + e.Message, e);
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return null;
        }

        /// <summary>
        /// Stores player information in DB.
        /// </summary>
        /// <param name="character">Reference to Character (may be a Player)</param>
        internal void SaveCharacter(int gameID, Character character)
        {
            SqlConnection cn = null;
            
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string player_cards = "''";
                if (character is Player)
                {
                    player_cards = "'";
                    foreach (Card card in ((Player)character).Cards)
                    {
                        player_cards += card.Name;
                        if (((Player)character).Cards.IndexOf(card) != ((Player)character).Cards.Count - 1)
                            player_cards += "|";
                    }
                    player_cards += "'";
                }

                string sql = "UPDATE player SET " +
                    " character_id = " + (int)character.CharacterType +
                    ", player_human = '" + (character is Player ? "True" : "False") + "'" +
                    ", player_name = " + (character is Player ? "'" + ((Player)character).PlayerName + "'" : "NULL") +
                    ", player_cards = " + (player_cards == null ? "" : player_cards) +
                    ", player_position_x = " + character.XPosition +
                    ", player_position_y = " + character.YPosition +
                    ", player_status = " + (int)character.Status +
                    " WHERE player_id = " + character.PlayerID;
                SqlCommand comm = new SqlCommand(sql, cn);
                int count = comm.ExecuteNonQuery();
                if (count != 1)
                {
                    throw new ClueLessExceptions("Failed to store the envelope.", null);
                }
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Stores content of the envelope.
        /// </summary>
        /// <param name="gameID">Game ID</param>
        /// <param name="env">Envelope</param>
        internal void SaveEnvelope(int gameID, Envelope env)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string sql = "UPDATE game SET envelope_weapon = '" + env.SuspectWeapon.Name + "', envelope_room = '" + env.SuspectRoom.Name + "'" +
                    ", envelope_character = '" + env.SuspectCharacter.Name + "' WHERE game_id = " + gameID;
                SqlCommand comm = new SqlCommand(sql, cn);
                int count = comm.ExecuteNonQuery();
                if (count != 1)
                {
                    throw new ClueLessExceptions("Failed to store the envelope.", null);
                }
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }

        /// <summary>
        /// Returns positions of weapons in the game
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns>Array of positions</returns>
        internal WeaponPosition[] GetWeaponPositions(int gameID)
        {
            SqlDataReader dr = null;
            List<WeaponPosition> items = new List<WeaponPosition>();
            try
            {
                string sql = "SELECT rope_location_x, rope_location_y, " +
                    "lead_pipe_location_x, lead_pipe_location_y, " +
                    "knife_location_x, knife_location_y, " +
                    "wrench_location_x, wrench_location_y, " +
                    "candlestick_location_x, candlestick_location_y, " +
                    "pistol_location_x, pistol_location_y " +
                    "FROM game WHERE game_id = " + gameID; 
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                dr.Read();
                items.Add(new WeaponPosition(dr.GetInt16(0), dr.GetInt16(1), Weapons.Rope));
                items.Add(new WeaponPosition(dr.GetInt16(2), dr.GetInt16(3), Weapons.LeadPipe));
                items.Add(new WeaponPosition(dr.GetInt16(4), dr.GetInt16(5), Weapons.Knife));
                items.Add(new WeaponPosition(dr.GetInt16(6), dr.GetInt16(7), Weapons.Wrench));
                items.Add(new WeaponPosition(dr.GetInt16(8), dr.GetInt16(9), Weapons.Candlestick));
                items.Add(new WeaponPosition(dr.GetInt16(10), dr.GetInt16(11), Weapons.Pistol));
                dr.Close();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Failed to load the list. " + e.Message, e);
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return items.ToArray();
        }

        /// <summary>
        /// Returns array of chat messages
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        internal ChatMessage[] GetChatMessages(int gameID)
        {
            SqlDataReader dr = null;
            List<ChatMessage> items = new List<ChatMessage>();
            try
            {
                string sql = "SELECT player.player_name, chat.chat_dt, chat.chat_message " +
                        "FROM chat LEFT OUTER JOIN player ON chat.player_id = player.player_id " +
                        "WHERE chat.game_id = " + gameID + 
                        " ORDER BY chat_dt DESC";
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    items.Add(new ChatMessage((dr.IsDBNull(0) ? "Admin" : dr.GetString(0)), dr.GetDateTime(1), dr.GetString(2)));
                }
                dr.Close();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Failed to load the list. " + e.Message, e);
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return items.ToArray();
        }

        /// <summary>
        /// Returns positions of weapons in the game
        /// </summary>
        /// <param name="gameID"></param>
        internal void SaveWeaponPositions(int gameID, WeaponPosition position)
        {
            SqlConnection cn = null;
            try
            {
                cn = new SqlConnection(ConnectionString);
                cn.Open();

                string name = "";
                switch (position.Weapon)
                {
                    case Weapons.Candlestick: name = "candlestick"; break;
                    case Weapons.Knife: name = "knife"; break;
                    case Weapons.LeadPipe: name = "lead_pipe"; break;
                    case Weapons.Pistol: name = "pistol"; break;
                    case Weapons.Rope: name = "rope"; break;
                    case Weapons.Wrench: name = "wrench"; break;
                    case Weapons.Empty: return;
                }

                string sql = "UPDATE game SET " + name + "_location_x = " + position.XPosition +
                    ", " + name + "_location_y = " + position.YPosition + " WHERE game_id = " + gameID;
                SqlCommand comm = new SqlCommand(sql, cn);
                int count = comm.ExecuteNonQuery();
                if (count != 1)
                {
                    throw new ClueLessExceptions("Failed to store the envelope.", null);
                }
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions(e.Message, e);
            }
            finally
            {
                if (cn != null && cn.State != ConnectionState.Closed)
                    cn.Close();
            }
        }



        /// <summary>
        /// Generic function that returns SQL is array of ComboBox items. First item in a query will correspond
        /// to a value and the second to a label.
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private ComboBoxItem[] GetList(string sql)
        {
            SqlDataReader dr = null;
            List<ComboBoxItem> items = new List<ComboBoxItem>();
            try
            {
                SqlConnection cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    string value = (dr.IsDBNull(0) ? "" : dr.GetString(0));
                    string label = (dr.IsDBNull(1) ? "" : dr.GetString(1));
                    items.Add(new ComboBoxItem(value, label));
                }

                dr.Close();
            }
            catch (Exception e)
            {
                throw new ClueLessExceptions("Failed to load the list. " + e.Message, e);
            }
            finally
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            return items.ToArray();
        }

        #region Misc functions

        /// <summary>
        /// Create an md5 sum string of this string
        /// </summary>
        /// <param name="str">String to create md5 sum for</param>
        /// <returns>checksum</returns>
        internal string GetMd5Sum(string str)
        {
            // First we need to convert the string into bytes, which
            // means using a text encoder.
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();

            // Create a buffer large enough to hold the string
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);

            // Now that we have a byte array we can ask the CSP to hash it
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);

            // Build the final string by converting each byte
            // into hex and appending it to a StringBuilder
            StringBuilder sb = new StringBuilder();
            for (int i=0;i<result.Length;i++)
            {
                sb.Append(result[i].ToString("X2"));
            }

            // And return it
            return sb.ToString();
        }

        /// <summary>
        /// Gets checksum for chat array
        /// </summary>
        /// <param name="chat"></param>
        /// <returns></returns>
        internal string GetChatChecksum(ChatMessage[] chat)
        {
            StringBuilder sb = new StringBuilder();
            foreach (ChatMessage message in chat)
            {
                sb.AppendLine(message.Message);
            }
            return GetMd5Sum(sb.ToString());
        }

        /// <summary>
        /// Gets checksum for character array
        /// </summary>
        /// <param name="characters"></param>
        /// <returns></returns>
        internal string GetCharactersChecksum(Character[] characters)
        {
            StringBuilder sb = new StringBuilder();
            foreach (Character character in characters)
            {
                sb.AppendLine(character.PlayerID.ToString() + character.XPosition.ToString() + character.YPosition.ToString());
            }
            return GetMd5Sum(sb.ToString());
        }
        #endregion
    }
}
